package com.ucom;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.LinkedList;

public class Query11
{	public class Tuple11 
	{	private String user_id;
		private int commands;

		public Tuple11(String user_id, int commands)
		{	super();
			this.user_id = user_id;
			this.commands = commands;
		}
	}

	private Connection dbConnection;
	private String query;
	private ResultSet rslt;
	private Statement stmtGetListEntries;

	public Query11 ()
	{	try 
		{	Class.forName("org.apache.derby.jdbc.ClientDriver");
			dbConnection = DriverManager.getConnection("jdbc:derby://localhost:1527/ucomm");
			stmtGetListEntries = dbConnection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

		} catch (ClassNotFoundException cnfe) {
			System.out.println("Error in creating Database conncetion");
			System.out.println(cnfe.getMessage());

		} catch (SQLException sqle) {
			System.out.println("Error in creating Database conncetion");
			System.out.println(sqle.getMessage());
		}

	}

	public LinkedList<Tuple11> query11() 
	{	LinkedList<Tuple11> result = new LinkedList<Tuple11>();
		try 
		{		query = "select user_id as \"user_id\", count(command) as \"commands\" "
					+	" from usage_history"
					+	" where user_id in (	select user_id" 
										+	" from user_info"
				           			  	+	" where dept_id in (	select dept_id"
				           			  							+	" from dept_info"
				           			  							+	" where dept_name='Computer Science')"
				           			  	+	" and enroll_date < '2009-11-19')"
				     +	" group by user_id";

				rslt = stmtGetListEntries.executeQuery(query);
				System.out.print("--------Query 11 result--------------------\n");
				while (rslt.next()) 
				{	System.out.print(rslt.getString(1));
					System.out.println("\t" + rslt.getInt(2));
					result.add(new Tuple11(rslt.getString(1), rslt.getInt(2)));
				}
				rslt.close();
				
		} 
		catch (SQLException sqle) 
		{	System.out.println("Error in getting transaction details");
			System.out.println(sqle.getMessage());
		} 
		catch (Exception e) 
		{	e.printStackTrace();
		}
		return result;
	}

}
